package com.towery.dbsrcmanager.db.service;

import com.towery.dbsrcmanager.es.entity.TDbObjectsEsVO;
import com.towery.dbsrcmanager.es.repository.DBObjectsRepository;
import com.towery.dbsrcmanager.git.service.GitSyncService;
import com.towery.dbsrcmanager.mybatis.base.dao.TDatabaseDAO;
import com.towery.dbsrcmanager.mybatis.base.dao.TDbObjectsDAO;
import com.towery.dbsrcmanager.mybatis.base.dao.TDbObjectsHisDAO;
import com.towery.dbsrcmanager.mybatis.base.dao.TDbObjectsTmpDAO;
import com.towery.dbsrcmanager.mybatis.base.entity.TDatabaseVO;
import com.towery.dbsrcmanager.mybatis.base.entity.TDbObjectsTmpVO;
import lombok.extern.slf4j.Slf4j;
import org.eclipse.jgit.api.errors.GitAPIException;
import org.elasticsearch.index.query.QueryBuilders;
import org.hibernate.engine.jdbc.internal.BasicFormatterImpl;
import org.hibernate.engine.jdbc.internal.DDLFormatterImpl;
import org.hibernate.engine.jdbc.internal.Formatter;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.elasticsearch.core.query.NativeSearchQueryBuilder;
import org.springframework.stereotype.Service;

import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * 数据库代码版本处理
 * Created by User on 2017/1/3.
 */
@Slf4j
@Service
public class VersionService {
    protected static final int BATCH_INSERT_LENGTH = 100;
    //数据库
    @Autowired
    TDatabaseDAO databaseDAO;
    //数据库对象
    @Autowired
    TDbObjectsTmpDAO tDbObjectsTmpDAO;
    @Autowired
    TDbObjectsHisDAO tDbObjectsHisDAO;
    @Autowired
    TDbObjectsDAO tDbObjectsDAO;

    @Autowired
    DBObjectsRepository dbObjectsRepository;

    @Autowired
    GitSyncService gitSyncService;

    Formatter formatter = new BasicFormatterImpl();
    Formatter ddlFormatter = new DDLFormatterImpl();

    //同步到临时表
    public boolean importDDLToTmp(String databaseId) {

        boolean result = true;

        TDatabaseVO databaseVO = databaseDAO.selectById(databaseId);

        try {
            DatabaseService dbsevice = new Db2DatabaseService(databaseVO);

            List<Map<String, Object>> objects = dbsevice.getObjects();
            //批量插入
            List<TDbObjectsTmpVO> batchQueue = new ArrayList<TDbObjectsTmpVO>();
            if (objects != null && objects.size() > 0) {

                tDbObjectsTmpDAO.deleteByDbId(databaseId);

                for (Map<String, Object> object : objects) {
                    TDbObjectsTmpVO tmpVO = new TDbObjectsTmpVO();
                    tmpVO.setDatabaseid((String) object.get("DATABASEID"));
                    tmpVO.setObjectname((String) object.get("OBJECTNAME"));
                    tmpVO.setObjecttype((String) object.get("OBJECTTYPE"));
                    tmpVO.setObjectscema((String) object.get("OBJECTSCEMA"));
                    tmpVO.setCreatetime((Date) object.get("CREATETIME"));
                    tmpVO.setUpdatetime((Date) object.get("UPDATETIME"));

                    if (object.get("DDL") != null) {
                        tmpVO.setDdl((String) object.get("DDL"));
                        if ("VIEW".equals(tmpVO.getObjecttype())) {
                            tmpVO.setDdl(formatter.format(tmpVO.getDdl()));
                        }

                    }
                    if (tmpVO.getDdl() == null && "TABLE".equals(tmpVO.getObjecttype())) {
                        tmpVO.setDdl(ddlFormatter.format(dbsevice.getTableDDL(tmpVO.getObjectscema(), tmpVO.getObjectname(), tmpVO.getRemark())).trim());
                    }
                    if (tmpVO.getDdl() == null) {
                        tmpVO.setDdl("");
                    }
                    batchQueue.add(tmpVO);

                    if (batchQueue.size() >= BATCH_INSERT_LENGTH) {
                        tDbObjectsTmpDAO.insertTmpObject(batchQueue);
                        batchQueue.clear();
                    }
                }
                if (batchQueue.size() > 0) {
                    tDbObjectsTmpDAO.insertTmpObject(batchQueue);
                    batchQueue.clear();
                }
            }
        } catch (Exception e) {
            log.error("", e);

            result = false;
        }


        return result;
    }


    //同步对象数据
    public boolean sync(String databaseid, boolean submit) {
        boolean result = true;
        //同步到临时表
        this.importDDLToTmp(databaseid);

        /*添加新增对象到历史表*/
        try {
            tDbObjectsHisDAO.insertNewObjectsToHis(databaseid);
        } catch (Exception e) {
            log.error("", e);
            result = false;
        }

          /*添加新增对象到当前数据表*/
        try {
            tDbObjectsDAO.insertNewObjectsToCurrent(databaseid);
        } catch (Exception e) {
            log.error("", e);
            result = false;
        }


           /*添加新增对象到历史表*/
        try {
            tDbObjectsHisDAO.insertUpdateObjectsToHis(databaseid);
        } catch (Exception e) {
            log.error("", e);
            result = false;
        }



          /*添加更新数据到当前表*/
        try {
            tDbObjectsDAO.updateObjectsToCurrent(databaseid);
        } catch (Exception e) {
            log.error("", e);
            result = false;
        }


         /*添加已删除对象到历史表*/
        try {
            tDbObjectsHisDAO.insertDeletedObjectsToHis(databaseid);
        } catch (Exception e) {
            log.error("", e);
            result = false;
        }

          /*添加已删除对象到当前表*/
        try {
            tDbObjectsDAO.updateDeletedObjectsToCurrent(databaseid);
        } catch (Exception e) {
            log.error("", e);
            result = false;
        }

        if (submit) {
            //清空索引
            dbObjectsRepository.deleteAll();

            List<TDbObjectsEsVO> esList = tDbObjectsDAO.getAll();
            //重置索引
            dbObjectsRepository.save(esList);


            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
            for (TDbObjectsEsVO es : esList) {

                String filePath = es.getDatabasename() + "/" + es.getObjectscema() + "/" + es.getObjecttype() + "/" + es.getObjectname();
                String version = filePath + "-" + sdf.format(es.getUpdatetime());

                try {
                    gitSyncService.commitFile(filePath + ".sql", es.getDdl(), version);
                } catch (IOException e) {
                    log.error("", e);
                } catch (GitAPIException e) {
                    log.error("", e);
                }

            }
            try {
                gitSyncService.pushCode();
            } catch (IOException e) {
                log.error("", e);
            } catch (GitAPIException e) {
                log.error("", e);
            }

        }
        return result;
    }

    /*取得数据源*/
    public TDatabaseVO getDatabase(String databaseId) {
        return databaseDAO.selectById(databaseId);
    }

/*
    public static void main(String[] args){
        Formatter formatter  =new BasicFormatterImpl();
        System.out.println(formatter.format("CREATE VIEW \"RMS\".\"CM_USERS\" AS select DISTINCT a.SUBJECT_CD,b.FULL_NAME from \"CASB\".\"CAS_SVC_APP_ACL\" a,\"CASB\".\"VW_CAS_USERS_ALL\" b where SVC_APP_ID=11 and ACCESS_LEVEL<>'-' and a.SUBJECT_ID=b.USER_ID"));
        formatter  =new DDLFormatterImpl();
        System.out.println(formatter.format("CREATE TABLE CASB.CAS_USERS_TPLT(USER_TPLT_ID INTEGER,USER_TPLT_TYPE VARCHAR(32),USER_CODE VARCHAR(64),USER_PSWD VARCHAR(64),FIRST_NAME VARCHAR(64),LAST_NAME VARCHAR(64),FULL_NAME VARCHAR(128),GENDER SMALLINT,IDENT_CAR_NO VARCHAR(64),BIRTHDAY VARCHAR(10),HIRE_DATE DATE,LEAVE_DATE DATE,OFFICE_PHONE VARCHAR(24),MOBILE_PHONE VARCHAR(24),OFFICE_EMAIL VARCHAR(64),PRIVY_EMAIL VARCHAR(64),PHOTO_FILE VARCHAR(128),ORG_CODE VARCHAR(64),COMP_CODE VARCHAR(64),COPI_ID INTEGER,USER_TYPE CHARACTER(1),ADMIN_BY INTEGER,ACTIVATE_TIME TIMESTAMP,LOCK_UNTIL TIMESTAMP,LAST_PWD_SET TIMESTAMP,PWD_NO_EXPIRE SMALLINT,BAD_PWD_CNT SMALLINT,FLAG_DISABLED SMALLINT,FLAG_BACK_AGENT SMALLINT,FLAG_BUILTIN SMALLINT,FLAG_READONLY SMALLINT,GEN_TYPE CHARACTER(1),ROW_SRC VARCHAR(8),CREATE_BY INTEGER,CREATE_TIME TIMESTAMP,MODIFY_BY INTEGER,MODIFY_TIME TIMESTAMP, PRIMARY KEY (USER_TPLT_TYPE));COMMENT          ON  CASB.CAS_USERS_TPLT(USER_TPLT_ID  IS  '外部用户的内部ID(>0)');COMMENT              ON  CASB.CAS_USERS_TPLT(USER_TPLT_TYPE  IS  '用户模版类别('ALL_SALE_ORG':所有现存特约店   'NEW_SALE_ORG':所有新增特约店');COMMENT                  ON  CASB.CAS_USERS_TPLT(USER_CODE  IS  '外部用户登录帐号');COMMENT                      ON  CASB.CAS_USERS_TPLT(USER_PSWD  IS  '外部用户登录密码');COMMENT                          ON  CASB.CAS_USERS_TPLT(FIRST_NAME  IS  '姓氏');COMMENT                              ON  CASB.CAS_USERS_TPLT(LAST_NAME  IS  '名字');COMMENT                                  ON  CASB.CAS_USERS_TPLT(FULL_NAME  IS  '姓名');COMMENT                                      ON  CASB.CAS_USERS_TPLT(GENDER  IS  '性别 (1:男性  0:女性)');COMMENT                                          ON  CASB.CAS_USERS_TPLT(IDENT_CAR_NO  IS  '身份证号码');COMMENT                                              ON  CASB.CAS_USERS_TPLT(BIRTHDAY  IS  '出生年月(YYYYMM)/日期(YYYYMMDD)');COMMENT                                                  ON  CASB.CAS_USERS_TPLT(HIRE_DATE  IS  '入职日期');COMMENT                                                      ON  CASB.CAS_USERS_TPLT(LEAVE_DATE  IS  '离职日期');COMMENT                                                          ON  CASB.CAS_USERS_TPLT(OFFICE_PHONE  IS  '办公座机号码');COMMENT                                                              ON  CASB.CAS_USERS_TPLT(MOBILE_PHONE  IS  '个人手机号码');COMMENT                                                                  ON  CASB.CAS_USERS_TPLT(OFFICE_EMAIL  IS  '办公电子邮箱');COMMENT                                                                      ON  CASB.CAS_USERS_TPLT(PRIVY_EMAIL  IS  '个人电子邮箱');COMMENT                                                                          ON  CASB.CAS_USERS_TPLT(PHOTO_FILE  IS  '个人照片文件名称');COMMENT                                                                              ON  CASB.CAS_USERS_TPLT(ORG_CODE  IS  '所属组织机构的编码');COMMENT                                                                                  ON  CASB.CAS_USERS_TPLT(COMP_CODE  IS  '所属组织机构所在公司的编码');COMMENT                                                                                      ON  CASB.CAS_USERS_TPLT(COPI_ID  IS  '任职的标准岗位的内部ID');COMMENT                                                                                          ON  CASB.CAS_USERS_TPLT(USER_TYPE  IS  '用户类别('U'-普通用户   'A'-用户管理员   'R'-系统根管理员)');COMMENT                                                                                              ON  CASB.CAS_USERS_TPLT(ADMIN_BY  IS  '此用户当前的上级管理员');COMMENT                                                                                                  ON  CASB.CAS_USERS_TPLT(ACTIVATE_TIME  IS  '此用户的首次登录激活时间(即首次登录更改密码的时间)');COMMENT                                                                                                      ON  CASB.CAS_USERS_TPLT(LOCK_UNTIL  IS  '此用户临时锁定的截止时间');COMMENT                                                                                                          ON  CASB.CAS_USERS_TPLT(LAST_PWD_SET  IS  '密码最后设置时间');COMMENT                                                                                                              ON  CASB.CAS_USERS_TPLT(PWD_NO_EXPIRE  IS  '标识-密码永不过期/永远有效');COMMENT                                                                                                                  ON  CASB.CAS_USERS_TPLT(BAD_PWD_CNT  IS  '密码连续出错次数');COMMENT                                                                                                                      ON  CASB.CAS_USERS_TPLT(FLAG_DISABLED  IS  '用户禁用标志');COMMENT                                                                                                                          ON  CASB.CAS_USERS_TPLT(FLAG_BACK_AGENT  IS  '标志－是不是后端自动调度处理代理帐户');COMMENT                                                                                                                              ON  CASB.CAS_USERS_TPLT(FLAG_BUILTIN  IS  '标志-内置固定组织机构');COMMENT                                                                                                                                  ON  CASB.CAS_USERS_TPLT(FLAG_READONLY  IS  '标志-是否只读(禁止修改)');COMMENT                                                                                                                                      ON  CASB.CAS_USERS_TPLT(GEN_TYPE  IS  '记录行生成方式('A'-自动计算  'M'-人工维护)');COMMENT                                                                                                                                          ON  CASB.CAS_USERS_TPLT(ROW_SRC  IS  '记录行生成来源');COMMENT                                                                                                                                              ON  CASB.CAS_USERS_TPLT(CREATE_BY  IS  '此记录行创建者用户ID');COMMENT                                                                                                                                                  ON  CASB.CAS_USERS_TPLT(CREATE_TIME  IS  '此用户的创建时间');COMMENT                                                                                                                                                      ON  CASB.CAS_USERS_TPLT(MODIFY_BY  IS  '此记录行最后修改者用户ID');COMMENT                                                                                                                                                          ON  CASB.CAS_USERS_TPLT(MODIFY_TIME  IS  '此用户信息的最后修改时间');"));

    }
    */

    public Page<TDbObjectsEsVO> getSearchResult(String quertString, int page, int pageSize) {

        Pageable pageable = new PageRequest(page, pageSize);
        /*
        NativeSearchQueryBuilder builder = new NativeSearchQueryBuilder()
                .withFields("objectname", "objecttype", "objectname", "objectscema", "ddl")
                .withQuery(QueryBuilders.fuzzyQuery("ddl", quertString))
                .withPageable(pageable);
*/

/*
        NativeSearchQuery query =new NativeSearchQuery(builder.build().getQuery(),null,null ,
                new HighlightBuilder.Field[]{
                        new HighlightBuilder.Field("ddl")
                                .preTags("<span style=\"color:red\">")
                                .postTags("</span>")
                }

        );
        */
        //return dbObjectsRepository.search(builder.build());
              return dbObjectsRepository.findByDdlContaining(quertString ,pageable);
    }
}
